S09-06 MySQL-用户权限管理

[TOC]

MySQL 的~~用户权限管理~~是一套从身份识别、权限层级划分到动态鉴权的严密访问控制体系（DAC / RBAC）。按照从基础认知到生产进阶的路径，整个知识体系结构可以拆解为以下七个核心阶段。

核心概念

MySQL 的权限管理机制建立在自主访问控制（DAC）与基于角色的访问控制（RBAC）模型之上。其核心逻辑由身份标识、认证机制、权限层级、类型划分、元数据字典与鉴权机制六大基础维度构成。

账户标识

MySQL 的账户认证并非单以用户名区分，而是严格绑定用户所在的主机网络位置，构成唯一的复合标识：

text

'用户名'@'主机名/IP地址'

账户独立性：相同用户名若绑定不同主机（例如 'app'@'localhost' 与 'app'@'192.168.1.%'），在 MySQL 内部被视为两个彼此完全独立的实体，各自拥有独立的密码、插件与权限集合。
主机匹配类型：
- localhost：指定本地通信。在 Linux 系统下默认直接使用 Unix Domain Socket 建立连接；在 Windows 下默认走命名管道或共享内存，绕过 TCP/IP 协议栈。
- 127.0.0.1：强制通过本地 TCP/IP 环回网卡通信。
- %：通配符，允许来自除 localhost 以外的任何远程 IP 发起连接。
- 192.168.1.% / 10.0.0.0/255.255.0.0：支持网段通配符或指定子网掩码，限定特定局域网段接入。
匹配优先级（Specificity Rule）：
当客户端发起连接时，MySQL 按照 Host 的具体程度从精确到模糊排序（非通配符优先于通配符）。若同时存在 'app'@'192.168.1.10' 与 'app'@'192.168.1.%'，来自 192.168.1.10 的请求会优先命中前者。
sql
```
-- 查询当前连接的客户端声明身份与实际鉴权身份
SELECT
  USER(),
  CURRENT_USER();

-- 查询系统已注册账户的主机与认证插件
SELECT
  user,
  host,
  plugin
  FROM mysql.user
  ORDER BY
    user,
    host;
```
1
2
3
4
5
6
7
8
9
10
11
12
13
14

认证机制

身份认证是鉴权的前置步骤，负责验证客户端凭证的真实性。MySQL 通过可插拔认证插件（Authentication Plugins）实现多样化的认证策略。

核心认证插件：
- caching_sha2_password：MySQL 8.0 默认认证插件。采用 SHA-256 多轮哈希加盐算法，引入服务端内存缓存机制加速后续连接验证，安全性显著高于早期版本。
- mysql_native_password：历史默认插件，基于双重 SHA-1 哈希算法，由于安全性较弱已在后续演进中逐步被弃用。
- auth_socket：直接映射操作系统本地用户的 UID/GID 进行免密登录，常见于 Linux 环境下的 root 初始安装配置。

密码生命周期与双密码机制：

支持配置密码过期时间、密码重用限制（历史密码比对）、密码复杂度校验，以及在不中断业务的前提下实现平滑更换密码的双密码（Dual Passwords）过渡机制。

sql

-- 指定认证插件并设置强哈希密码
CREATE USER 'developer'@'192.168.1.%'
  IDENTIFIED WITH caching_sha2_password
  BY 'SecurePass#2026';

-- 启用双密码过渡机制以支持平滑轮转
ALTER USER 'developer'@'192.168.1.%'
  IDENTIFIED BY 'NewPass#2026'
  RETAIN CURRENT PASSWORD;

权限层级

MySQL 的权限采用树状多级继承结构，权限范围沿层级自顶向下收敛。

层级名称	作用范围	语法表示	核心存储字典	典型权限类型
全局级 (Global)	整个 MySQL 实例的所有数据库及系统操作	`.`	`mysql.user`	`SHUTDOWN`, `PROCESS`, `SUPER`
数据库级 (Database)	某个指定数据库下的所有表、视图与例程	`db_name.*`	`mysql.db`	`CREATE`, `DROP`, `ALTER`, `SHOW VIEW`
表级 (Table)	某张指定数据表内的全部列数据	`db_name.tbl_name`	`mysql.tables_priv`	`SELECT`, `INSERT`, `UPDATE`, `INDEX`
列级 (Column)	指定数据表中的特定一个或多个列	`(col1, col2)`	`mysql.columns_priv`	`SELECT(col)`, `UPDATE(col)`
例程级 (Routine)	指定的单个存储过程或存储函数	`PROCEDURE / FUNCTION`	`mysql.procs_priv`	`EXECUTE`, `ALTER ROUTINE`

高层级授予的权限具有全局覆盖性。例如：在全局级赋予了 SELECT ON *.*，则无需在数据库级或表级重复授权，用户天然对所有库表具备只读能力。

权限分类

根据操作对象和安全影响，MySQL 权限可归纳为以下功能类型：

数据操作权限 (DML)：
- SELECT：读取表或视图中的记录。
- INSERT：向表中插入新记录。
- UPDATE：修改表中已存在的字段数据。
- DELETE：删除表中的行记录。
结构定义权限 (DDL)：
- CREATE / DROP：创建或删除数据库、数据表、视图及索引。
- ALTER：变更现有表结构（增删字段、修改约束）。
- INDEX：创建或删除数据表索引。
管理控制权限 (DCL/Admin)：
- GRANT OPTION：允许将自身拥有的权限再次转授给其他账户。
- RELOAD：允许刷新日志、权限表、清空缓存（如 FLUSH TABLES）。
- PROCESS：允许通过 SHOW PROCESSLIST 查看其他所有会话执行的 SQL 语句。
- FILE：允许使用 LOAD DATA INFILE 及 SELECT ... INTO OUTFILE 读写服务端本地磁盘文件。

动态权限

MySQL 8.0 引入了动态权限（Dynamic Privileges）架构，用以解耦过去功能过于庞大且不可拆分的单体超级权限 SUPER。

静态权限 vs 动态权限：
- 静态权限：编译进 MySQL Server 内核二进制代码中，通过位掩码（Bitmask）存储在 mysql.user 的固定列中，无法在运行时扩展。
- 动态权限：在服务运行时动态注册，持久化保存在系统表 mysql.global_grants 中。不仅内核支持按需加载，第三方插件与组件（Component）也能注册专属的动态权限。
常见细分动态权限：
- SYSTEM_VARIABLES_ADMIN：修改全局系统变量，无需赋予整套超级管理员权限。
- CONNECTION_ADMIN：终止其他客户端会话，管理连接数阈值。
- BACKUP_ADMIN：执行备份锁定操作（如 LOCK INSTANCE FOR BACKUP）。
- PERSIST_RO_VARIABLES_ADMIN：修改并持久化只读系统变量到 mysqld-auto.cnf。
sql
```
-- 为运维账户授予精细化的系统管理与连接管理动态权限
GRANT CONNECTION_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.*
  TO 'ops_user'@'10.0.%.%';

-- 查询当前实例已注册的所有动态权限记录
SELECT
  user,
  host,
  privilege
  FROM mysql.global_grants
  WHERE
    user = 'ops_user';
```
1
2
3
4
5
6
7
8
9
10
11
12

权限字典

MySQL 在名为 mysql 的系统元数据字典库中维护用户与权限的基础数据。服务器启动或执行授权语句时，会将这些数据加载至内存哈希结构中加速访问。

mysql.user：记录账户基础标识、认证插件、密码哈希、账户生命周期属性以及所有全局级静态权限。
mysql.db：记录账户在数据库级的作用范围及权限配置。
mysql.tables_priv：记录账户在表级的权限，并存储该表是否包含列级权限的掩码信息。
mysql.columns_priv：记录具体到某一数据列的细粒度操作权限（仅支持 SELECT、INSERT、UPDATE、REFERENCES）。
mysql.procs_priv：记录存储过程（Procedure）与存储函数（Function）的执行与修改权限。
mysql.global_grants：专门记录赋予账户的动态全局权限。

鉴权流程

客户端与 MySQL 通信全生命周期的权限校验划分为连接鉴权与请求鉴权两个严谨的阶段。

短路放行原则：鉴权检测自顶向下展开，只要在较高层级（如全局级或库级）匹配到目标 SQL 所需的完整权限，MySQL 立即判定合法并放行，不再消耗性能去遍历表级或列级字典。
完全覆盖原则：若执行涉及多表多列的复杂查询（如 JOIN 查询或子查询），MySQL 要求参与该操作的所有物理对象在对应层级均具备相应权限，任意单项缺失均会导致整条 SQL 鉴权失败。

安全规范

在生产环境构建 MySQL 权限体系时，建议遵循以下行业标准原则：

最小权限原则（Principle of Least Privilege）：
- 业务应用账户严禁授予 SUPER、FILE、SHUTDOWN、GRANT OPTION 等高危管理权限。
- 业务应用账户通常仅开放 SELECT, INSERT, UPDATE, DELETE，严禁开放 DROP, TRUNCATE 或 ALTER 等 DDL 权限（DDL 应通过专门的发布通道执行）。
账户职责分离：
- 只读分析账户：仅赋予从库的 SELECT 权限。
- 数据迁移账户：分配特定库的 REPLICATION CLIENT 与 DDL/DML 权限。
- 业务写入账户：绑定特定应用服务器网段 IP，仅赋予目标库的 DML 权限。
收敛根账户与网络范围：
- 禁用 root 用户的远程连接，强制修改 root 主机为 localhost。
- 杜绝将业务账户的主机设为 %，必须精确到内网子网（如 'app'@'10.20.%.%'）。

用户管理

MySQL 的用户管理是整个权限体系的入口，负责维护数据库用户的完整生命周期，包括创建、认证配置、属性变更、安全策略管控以及账户销毁。

用户创建

创建账户通过 CREATE USER 语句完成。MySQL 采用复合标识 '用户名'@'主机名' 确定用户身份，支持配置默认认证插件、初始密码与账户备注。

sql

-- 创建指定内网IP段的业务账户并添加备注
CREATE USER 'order_service'@'192.168.10.%'
  IDENTIFIED WITH caching_sha2_password BY 'Order@2026_Secure'
  COMMENT '订单微服务专用数据库账户';

-- 创建本地临时开发账户并强制首次登录修改密码
CREATE USER 'dev_temp'@'localhost'
  IDENTIFIED BY 'Temp#Init123'
  PASSWORD EXPIRE;

主机名匹配：192.168.10.% 限制仅允许该子网内的主机连接；localhost 强制使用本地 Unix Socket 或命名管道。
默认认证插件：MySQL 8.0 默认使用 caching_sha2_password，具备更高强度的密码加盐哈希算法和连接缓存加速机制。
PASSWORD EXPIRE：将账户标记为密码已过期，用户成功建立初次连接后必须重置密码才能执行任何业务 SQL。

信息修改

账户信息发生变动时，无需删除重建，可通过 RENAME USER 修改账户名或主机名，通过 ALTER USER 调整认证机制与扩展属性。

sql

-- 重命名账户用户名与所属主机
RENAME USER 'dev_temp'@'localhost'
  TO 'dev_qa'@'127.0.0.1';

-- 修改账户的认证插件与备注信息
ALTER USER 'dev_qa'@'127.0.0.1'
  IDENTIFIED WITH caching_sha2_password BY 'QA_NewPass#2026'
  COMMENT 'QA测试环境专用账户';

原子操作保证：RENAME USER 支持在单条语句中重命名多个账户，具备事务原子性，且会自动将原用户绑定的权限整体迁移至新账户名下。
属性热更新：ALTER USER 执行后立即刷新内存中的账户缓存，已建立的会话不受影响，后续新连接按新配置鉴权。

密码管理

MySQL 提供完善的密码安全策略体系，覆盖有效周期、历史复用限制以及防暴力破解锁定机制。

sql

-- 设定密码90天过期、禁止复用最近5次密码及输错3次自动锁定1天
ALTER USER 'order_service'@'192.168.10.%'
  PASSWORD EXPIRE INTERVAL 90 DAY
  PASSWORD HISTORY 5
  PASSWORD REUSE INTERVAL 365 DAY
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 1;

-- 覆盖全局策略并将特定账户设为密码永不过期
ALTER USER 'order_service'@'192.168.10.%'
  PASSWORD EXPIRE NEVER;

周期过期：PASSWORD EXPIRE INTERVAL N DAY 强制要求客户端定期更换凭证，逾期未更换则拒绝执行查询。
历史防复用：PASSWORD HISTORY N（近 N 次不可用）配合 PASSWORD REUSE INTERVAL M DAY（M 天内不可用），双重阻断旧密码轮流交替使用。
自动封禁防护：FAILED_LOGIN_ATTEMPTS 连续输错密码达到阈值后，系统将自动锁定账户 PASSWORD_LOCK_TIME 天（或设为 UNBOUNDED 永久锁定直至管理员介入）。

双密码机制

在分布式微服务架构中，直接修改数据库密码会导致未更新配置的旧应用节点瞬间大面积断连。MySQL 8.0 提供了双密码功能（Dual Passwords），支持在不停机的情况下完成凭证无缝轮转。

轮转标准操作流程如下：

追加从密码：保留当前运行的密码作为辅助密码，同时写入全新主密码。
滚动更新应用：逐步重启或热更新各微服务节点的数据库连接配置，各节点使用新旧密码均能正常建立连接。

废弃旧密码：待全部客户端均切换至新密码后，清理辅助密码，完成闭环。

sql

-- 阶段一：设置新主密码并保留当前密码为辅助密码
ALTER USER 'order_service'@'192.168.10.%'
  IDENTIFIED BY 'NextGen#2026_Secure'
  RETAIN CURRENT PASSWORD;

-- 阶段三：全量微服务节点升级完毕后销毁旧密码
ALTER USER 'order_service'@'192.168.10.%'
  DISCARD OLD PASSWORD;

状态锁定

管理员可通过状态控制手动挂起或恢复账户，常用于离职交接、安全审计或应急响应场景。

sql

-- 手动锁定账户禁止建立任何新连接
ALTER USER 'dev_qa'@'127.0.0.1'
  ACCOUNT LOCK;

-- 解除锁定恢复账户正常访问权限
ALTER USER 'dev_qa'@'127.0.0.1'
  ACCOUNT UNLOCK;

锁定与过期的区别：
- 锁定（LOCKED）：用户在握手建立连接阶段即被直接拒绝，完全无法登录实例。
- 过期（EXPIRED）：用户能够成功登录并建立会话，但在执行任何数据查询前会被服务端强制拦截，必须先执行 ALTER USER USER() IDENTIFIED BY ... 修改密码。

资源限制

为了防止单个账户因异常代码、恶意慢查询或连接泄漏耗尽数据库服务器资源，可以在用户级别配置资源配额（Resource Limits）。

sql

-- 对分析账户设置每小时最大查询数与最大并发连接数
ALTER USER 'bi_reporter'@'192.168.10.%'
  WITH MAX_QUERIES_PER_HOUR 50000
  MAX_UPDATES_PER_HOUR 0
  MAX_CONNECTIONS_PER_HOUR 1000
  MAX_USER_CONNECTIONS 10;

MAX_QUERIES_PER_HOUR：每小时允许执行的最大查询语句总量（无论成功或失败均计数）。
MAX_UPDATES_PER_HOUR：每小时允许执行的最大数据修改语句总量（设为 0 表示不单独限制更新，仅受总查询限制）。
MAX_CONNECTIONS_PER_HOUR：每小时允许发起的新建连接请求次数上限。
MAX_USER_CONNECTIONS：同一时刻允许此账户保有的最大并发活跃连接数，超额将被拒绝。

用户销毁

当账户不再使用时，必须及时将其从系统元数据中移除，防止出现孤儿账户或安全敞口。

sql

-- 批量安全删除不再使用的账户
DROP USER IF EXISTS 'dev_qa'@'127.0.0.1', 'bi_reporter'@'192.168.10.%';

-- 检查系统字典表确认账户已被彻底清理
SELECT
  user,
  host,
  account_locked
  FROM mysql.user
  WHERE
    user IN ('dev_qa', 'bi_reporter');

级联影响：执行 DROP USER 会自动回收该用户在所有层级（全局、库、表、列、例程）持有的全部权限记录，并同步清理其拥有的角色关联。
存储对象所有者检查：删除用户前，需排查该用户是否作为 DEFINER 创建了视图（View）、触发器（Trigger）、存储过程（Procedure）或事件调度器（Event）。若直接删除，相关对象的执行可能会因找不到定义者而报权限失败错误。

授权与回收

在 MySQL 的安全架构中，授权（GRANT） 与 回收（REVOKE） 构成了数据控制语言（DCL）的核心。它们负责为已认证的用户或角色绑定、变更或撤销在各层级对象上的操作特权。

语法模型

授权与回收采用对称的语法规范，明确定义了“谁在哪个层级对什么对象拥有何种操作能力”。

sql

-- 授权基础语法结构
GRANT SELECT, INSERT ON shop_db.*
  TO 'app_user'@'192.168.1.%';

-- 回收基础语法结构
REVOKE INSERT ON shop_db.*
  FROM 'app_user'@'192.168.1.%';

目标对象（ON）：指定权限生效的资源边界，支持实例全局、库、表、列及例程对象。
主体对象（TO / FROM）：指定被授权或被回收的目标用户（'user'@'host'）或角色名。
幂等性与增量合并：多次执行 GRANT 语句不会覆盖已有权限，而是将新权限增量追加至现有权限集合中。

多级授权

MySQL 按照对象粒度从粗到细提供多级授权能力，高层级权限对低层级对象天然继承生效。

sql

-- 全局级授权：对整个实例所有库表及系统级指令生效
GRANT SELECT, PROCESS ON *.*
  TO 'monitor_user'@'192.168.1.%';

-- 数据库级授权：对指定业务库内所有对象生效
GRANT CREATE, ALTER, SELECT, INSERT, UPDATE, DELETE ON order_db.*
  TO 'app_user'@'192.168.1.%';

-- 表级授权：仅对特定数据表生效
GRANT SELECT, UPDATE ON order_db.orders
  TO 'audit_user'@'192.168.1.%';

全局级（*.*）：权限写入 mysql.user 表，授予所有数据库、表的数据操作能力及实例级管理特权（如 PROCESS、RELOAD）。
数据库级（db_name.*）：权限写入 mysql.db 表，控制用户在该数据库内部所有表、视图的 DDL 与 DML 行为。
表级（db_name.table_name）：权限写入 mysql.tables_priv 表，将特权收敛至单一数据表实体。

细粒度授权

除库表级别外，MySQL 允许将权限进一步下沉至数据列和存储例程。

sql

-- 列级授权：仅允许查询特定非敏感字段并修改昵称
GRANT SELECT (id, username, nickname), UPDATE (nickname) ON user_db.users
  TO 'profile_editor'@'192.168.1.%';

-- 例程级授权：仅允许执行指定的存储过程
GRANT EXECUTE ON PROCEDURE order_db.sp_settle_monthly_bill
  TO 'finance_user'@'192.168.1.%';

列级权限约束：列级授权仅支持 SELECT、INSERT、UPDATE 和 REFERENCES 四种操作，元数据持久化在 mysql.columns_priv。
例程级权限（Routine）：通过指定 PROCEDURE 或 FUNCTION，将 EXECUTE（执行权限）与 ALTER ROUTINE（修改/删除过程定义权限）精准下发，元数据保存在 mysql.procs_priv。

权限转授

在授权语句末尾追加 WITH GRANT OPTION，允许被授权者将自身所持有的权限再次转授给第三方账户。

sql

-- 赋予库级管理权限并允许将自身权限二次转授他人
GRANT ALL PRIVILEGES ON project_db.*
  TO 'team_lead'@'192.168.1.%'
  WITH GRANT OPTION;

-- 仅回收转授权限而保留既有数据操作权限
REVOKE GRANT OPTION ON project_db.*
  FROM 'team_lead'@'192.168.1.%';

安全边界：被授权者只能转授自身已明确拥有的权限，无法转授自身未持有的特权。
非级联回收机制：与标准 SQL 的级联回收（CASCADE）不同，在 MySQL 中回收 'team_lead' 的权限时，'team_lead' 过去已经转授给其他用户的权限不会被自动级联撤销，必须由管理员显式审计并手动回收。

权限回收

回收权限时，ON 子句声明的层级必须与当初授予时的层级严格一致，否则系统无法定位目标记录。

sql

-- 针对性回收特定表的物理删除权限
REVOKE DELETE ON order_db.orders
  FROM 'app_user'@'192.168.1.%';

-- 一键清空账户的所有全局及各级权限与转授能力
REVOKE ALL PRIVILEGES, GRANT OPTION
  FROM 'temp_contractor'@'192.168.1.%';

-- 在开启 partial_revokes 后对特定子库实施限制性回收
REVOKE SELECT ON sensitive_db.*
  FROM 'global_reader'@'192.168.1.%'
  RESTRICT;

层级对应原则：若在 *.* 全局级授予了 SELECT，直接执行 REVOKE SELECT ON order_db.* 会报错，因为在库级元数据表中找不到该记录。
限制性部分回收（Partial Revokes）：MySQL 8.0 引入了 partial_revokes 系统变量。启用后，允许在拥有全局权限的前提下，针对特定数据库显式剔除访问能力，打破了以往“全局即全部”的局限。

权限审计

为确保系统符合最小权限原则，需定期审计账户与角色的权限清单。

sql

-- 查看指定用户当前显式拥有的全部权限清单
SHOW GRANTS FOR 'app_user'@'192.168.1.%';

-- 查看用户结合某个角色后生效的合并权限
SHOW GRANTS FOR 'alice'@'192.168.1.%'
  USING 'role_order_reader';

-- 从元数据字典中检索所有具备库级写权限的记录
SELECT
  grantor,
  grantee,
  table_schema,
  privilege_type
  FROM information_schema.schema_privileges
  WHERE
    privilege_type IN ('INSERT', 'UPDATE', 'DELETE');

SHOW GRANTS 输出解析：输出结果直接展示能够复现当前权限状态的标准 GRANT 语句序列。
字典视图审计：information_schema 库下的 USER_PRIVILEGES（全局）、SCHEMA_PRIVILEGES（数据库级）、TABLE_PRIVILEGES（表级）与 COLUMN_PRIVILEGES（列级）提供了便于编写自动化脚本过滤的结构化视图。

生效机制

理解权限生效与刷新的底层逻辑，有助于避免生产运维中的操作误区。

DCL 语句自动热更新：
执行 GRANT、REVOKE、CREATE ROLE、DROP ROLE 等标准 DCL 语句时，MySQL 服务端会实时同步修改内存中的权限哈希字典。
新旧连接的生效差异：
- 全局与库级权限变更：通常在新建立的会话中生效；部分正在运行的长连接可能无法感知已变更的全局权限。
- 表级与列级权限变更：在当前活跃会话执行下一条涉及该表的 SQL 查询时即可实时被拦截或放行。
FLUSH PRIVILEGES 真实场景：
该指令的作用是强制清空内存缓存并从磁盘上的 mysql 系统表重新拉取权限数据。仅在直接执行底层 DML（如 UPDATE mysql.user SET ...）绕过权限引擎时才需使用。日常使用标准 DCL 时严禁滥用 FLUSH PRIVILEGES，以免在高并发下触发全局元数据锁竞争。

角色体系

MySQL 8.0 引入了基于角色的访问控制（RBAC, Role-Based Access Control），通过将权限与具体用户解耦，将权限集合抽象为“角色”实体，实现用户权限的模块化、批量化与动态管理。

角色概念

角色（Role）在 MySQL 中本质上是一个命名权限集合。在底层存储实现中，角色与普通用户共用 mysql.user 系统表，但角色默认被赋予 account_locked = 'Y' 与 password_expired = 'Y' 属性，无法直接用于建立客户端连接。

传统授权痛点：直连用户的授权方式会导致权限碎片化。当组织架构变动或多人离职入职时，逐一修改每个账户的库表权限容易引发遗漏或权限漂移。
角色模型优势：管理员只需预定义标准角色（如只读分析员、应用开发、报表运营），后续仅需将对应角色指派给用户，即可完成权限的批量下发与统一回收。

角色管理

角色的生命周期管理包括创建、命名与销毁。

sql

-- 创建只读分析与后端开发角色
CREATE ROLE 'role_read_only', 'role_app_dev';

-- 删除废弃的角色
DROP ROLE 'role_read_only';

角色标识规则：角色遵循 'role_name'@'host_name' 命名规则。如果省略主机名，MySQL 默认将其补全为 'role_name'@'%'。
级联影响：删除角色（DROP ROLE）时，系统会自动清理该角色在所有用户身上的指派关系（从 mysql.roles_mapping 中移除），但已从该角色继承并被赋予其他角色的独立权限不会受损。

权限绑定

角色创建后默认不包含任何特权，需要通过标准 GRANT 语句为其挂载具体的层级权限。

sql

-- 为只读角色绑定数据库查询权限
GRANT SELECT ON shop_db.*
  TO 'role_read_only';

-- 回收角色的部分表操作权限
REVOKE DELETE ON shop_db.orders
  FROM 'role_app_dev';

权限类型支持：角色可以绑定包括全局级、数据库级、表级、列级、存储过程级在内的所有静态权限与动态管理权限。
热变更生效：当修改角色的权限定义后，当前已激活该角色的在线会话以及未来新建立的会话会立即同步应用最新权限。

角色指派

将配置好权限的角色分配给具体的用户账号，建立用户与角色的映射关系。

sql

-- 将角色批量指派给具体开发人员
GRANT 'role_app_dev'
  TO 'alice'@'192.168.1.%', 'bob'@'192.168.1.%';

-- 授予主管转授该角色的管理特权
GRANT 'role_app_dev'
  TO 'team_lead'@'192.168.1.%'
  WITH ADMIN OPTION;

-- 回收指定用户的角色
REVOKE 'role_app_dev'
  FROM 'bob'@'192.168.1.%';

多对多关联：一个用户可以同时被授予多个不同角色；一个角色也可以同时分配给任意数量的用户。
WITH ADMIN OPTION：类似于权限转授的 WITH GRANT OPTION。获得此选项的用户，有权将该角色二次授予或从其他用户身上回收，但不能直接修改角色本身的权限内容。

角色激活

将角色指派给用户后，默认处于非激活（Inactive）状态。用户登录实例时，并不会直接获得该角色的权限，必须经过显式激活。

sql

-- 为用户配置登录默认激活全部已有角色
SET DEFAULT ROLE ALL
  TO 'alice'@'192.168.1.%';

-- 会话内手动切换激活指定角色
SET ROLE 'role_app_dev';

-- 会话内临时挂起所有已激活角色
SET ROLE NONE;

-- 查看当前会话中处于激活生效状态的角色
SELECT
  CURRENT_ROLE();

默认角色配置（Default Role）：通过 SET DEFAULT ROLE 指定用户每次登录时自动加载生效的角色集合。
全局自动激活参数：设置 SET GLOBAL activate_all_roles_on_login = ON; 后，所有用户登录时将自动激活其拥有的全部角色，无需逐个执行 SET DEFAULT ROLE。
最小特权按需激活：在安全等级要求较高的环境中，可保持默认不激活，仅在执行敏感维护任务时通过 SET ROLE 显式临时提权。

角色继承

MySQL 支持角色的嵌套与层级化组合，允许将一个或多个基础角色指派给另一个复合角色，形成树状权限继承链。

sql

-- 创建基础读写角色与复合管理员角色
CREATE ROLE 'role_read', 'role_write', 'role_admin';

-- 分别为基础角色绑定对应权限
GRANT SELECT ON shop_db.*
  TO 'role_read';
GRANT INSERT, UPDATE, DELETE ON shop_db.*
  TO 'role_write';

-- 将基础角色合并继承至复合管理员角色
GRANT 'role_read', 'role_write'
  TO 'role_admin';

权限合并传递：用户被指派并激活 role_admin 时，将自动继承其子角色 role_read 与 role_write 包含的所有读写权限，避免重复配置通用权限。
循环引用检测：MySQL 具备循环继承检测机制，当发生类似 A 包含 B、B 又包含 A 的循环引用时，系统能自动识别图结构并正确处理闭包，不会导致鉴权死循环。

字典审计

角色的关联状态与有效性由系统底层元数据表及信息架构视图统一记录。

sql

-- 查看用户在激活特定角色后的组合生效权限
SHOW GRANTS FOR 'alice'@'192.168.1.%'
  USING 'role_app_dev';

-- 查询实例中所有角色与用户的映射关系
SELECT
  from_user AS role_name,
  to_user AS granted_user
  FROM mysql.roles_mapping;

-- 查询当前连接适用的角色及有效状态
SELECT
  role_name,
  is_default,
  is_mandatory
  FROM information_schema.applicable_roles;

mysql.roles_mapping：存储用户与角色、角色与子角色的映射与继承关系。
mysql.default_roles：记录每个用户的默认登录自动激活角色列表。
mandatory_roles 系统变量：支持在 my.cnf 中配置强制全局角色（如审计或只读兜底角色），该角色会自动赋权并强制激活于所有新建及现有用户身上。

S09-06 MySQL-用户权限管理 ​

核心概念 ​

账户标识 ​

认证机制 ​

权限层级 ​

权限分类 ​

动态权限 ​

权限字典 ​

鉴权流程 ​

安全规范 ​

用户管理 ​

用户创建 ​

信息修改 ​

密码管理 ​

双密码机制 ​

状态锁定 ​

资源限制 ​

用户销毁 ​

授权与回收 ​

语法模型 ​

多级授权 ​

细粒度授权 ​

权限转授 ​

权限回收 ​

权限审计 ​

生效机制 ​

角色体系 ​

角色概念 ​

角色管理 ​

权限绑定 ​

角色指派 ​

角色激活 ​

角色继承 ​

字典审计 ​